/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package co.springtree.spectrum.visa.persistence.dao;

import co.springtree.spectrum.visa.persistence.entities.CommandDTO;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import org.apache.log4j.Logger;

/**
 *
 * @author Ricardo
 */
public class CommandDAO extends AbstractDAO<CommandDTO>{

    private static Logger logger = Logger.getLogger("CommandDAO");
    @Override
    public void insert(CommandDTO object) throws SQLException {
        try {
            connection = getConnectionPool().getConnection();
            sQLQuery = "INSERT INTO command (commandname, visacommand) VALUES (?,?)";
            statement = connection.prepareStatement(sQLQuery);
            statement.setString(1, object.getCommandName());
            statement.setString(2, object.getVisaCommand());
            statement.executeUpdate();
        } catch (SQLException e) {
            logger.error(e);
        } finally {
            getConnectionPool().freeConnection(connection);
        }
    }

    @Override
    public void update(CommandDTO object) throws SQLException {
        try {
            connection = getConnectionPool().getConnection();
            sQLQuery = "UPDATE command SET commandname = ?, visacommand=?";
            statement = connection.prepareStatement(sQLQuery);
            statement.setString(1, object.getCommandName());
            statement.setString(2, object.getVisaCommand());
            statement.executeUpdate();
        } catch (SQLException e) {
           logger.error(e);
        } finally {
            getConnectionPool().freeConnection(connection);
        }
    }

    @Override
    public void delete(CommandDTO object) throws SQLException {
        try {
            connection = getConnectionPool().getConnection();
            sQLQuery = "DELETE FROM command WHERE commandname = ?";
            statement = connection.prepareStatement(sQLQuery);
            statement.setString(1, object.getCommandName());
            statement.executeUpdate();
        } catch (SQLException e) {
            logger.error(e);
        } finally {
            getConnectionPool().freeConnection(connection);
        }
    }

    @Override
    public CommandDTO select(CommandDTO commandSearch) throws SQLException {
        CommandDTO command = null;
        try {
            connection = getConnectionPool().getConnection();
            sQLQuery = "SELECT * FROM command WHERE commandName = ?";
            statement = connection.prepareStatement(sQLQuery);
            statement.setString(1, ((CommandDTO) commandSearch).getCommandName());
            rs = statement.executeQuery();
            if (rs.next()) {
                command = new CommandDTO();
                command.setCommandName(rs.getString("commandName"));
                command.setVisaCommand(rs.getString("visacommand"));
            }
        } catch (SQLException e) {
            logger.error(e);
        } finally {
            getConnectionPool().freeConnection(connection);
        }

        return command;
    }

    @Override
    public Collection<CommandDTO> selectAll() throws SQLException {
        Collection<CommandDTO> commandColl = new ArrayList<CommandDTO>();
        try {
            connection = getConnectionPool().getConnection();
            sQLQuery = "SELECT * FROM command";
            statement = connection.prepareStatement(sQLQuery);
            rs = statement.executeQuery();
            while (rs.next()) {
                CommandDTO command = new CommandDTO();
                command.setCommandName(rs.getString("commandName"));
                command.setVisaCommand(rs.getString("visacommand"));
                commandColl.add(command);
            }
        } catch (SQLException e) {
            logger.error(e);
        } finally {
            getConnectionPool().freeConnection(connection);
        }

        return commandColl;
    }

    @Override
    public void createTable(boolean dropTable) throws Exception {
        try {
            connection = getConnectionPool().getConnection();
            sQLQuery = null;
            if (dropTable == true) {
                sQLQuery = "DROP TABLE IF EXISTS command";
                statement = connection.prepareStatement(sQLQuery);
                statement.executeUpdate();
            }
            sQLQuery = "CREATE TABLE command ("
                    + "  commandname varchar(255) NOT NULL,"
                    + "  visacommand varchar(255) NOT NULL,"
                    + "  PRIMARY KEY (commandname))";

            statement = connection.prepareStatement(sQLQuery);
            statement.executeUpdate();
        } catch (SQLException e) {
            logger.error(e);
        } finally {
            getConnectionPool().freeConnection(connection);
        }

    }
    
}
